package com.swa99ylum.pxp.a.a007;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 测试 
 * sheet.getFirstRowNum()
 * sheet.getLastRowNum()
 * 结论：只要 该row 上任意一个 cell 有了一点内容（哪怕是一个 空格），都会认为 改行是 有效行，即
 * 1) XSSFRow row = sheet.getRow(i);
 * 2) row 不等于 null
 */
public class Test1 {
	
	/**
	 * 测试：1、getLastRowNum() VS getLastCellNum()
	 */
	public static void main00(String[] args) throws IOException {
		File file = new File("ccc.xlsx");
		System.out.println(file.getAbsolutePath());
		
		FileInputStream fis = new FileInputStream(file);
		XSSFWorkbook xwb = new XSSFWorkbook(fis);
		XSSFSheet sheet = xwb.getSheet("aaa");
		
		System.out.println("第 " + sheet.getFirstRowNum() + " 行");//0
		XSSFRow firstRow = sheet.getRow(sheet.getFirstRowNum());
		System.out.println(firstRow.getFirstCellNum() + " - " + firstRow.getLastCellNum());
		System.out.println(firstRow.getCell(firstRow.getFirstCellNum()) + " - " + firstRow.getCell(firstRow.getLastCellNum()));
		
		System.out.println("--------------------------------");
		
		System.out.println("第 " + sheet.getLastRowNum() + " 行");//1
		XSSFRow lastRow = sheet.getRow(sheet.getLastRowNum());
		System.out.println(lastRow.getFirstCellNum() + " - " + lastRow.getLastCellNum());
		System.out.println(lastRow.getCell(lastRow.getFirstCellNum()) + " - " + lastRow.getCell(lastRow.getLastCellNum()));
	}
	
	/**
	 * 测试：5、诡异的 透明单元格
	 */
	public static void main(String[] args) throws IOException {
		File file = new File("ccc.xlsx");
		System.out.println(file.getAbsolutePath());
		
		FileInputStream fis = new FileInputStream(file);
		XSSFWorkbook xwb = new XSSFWorkbook(fis);
		XSSFSheet sheet = xwb.getSheet("aaa2");
		
		System.out.println("第 " + sheet.getFirstRowNum() + " 行");//0
		XSSFRow firstRow = sheet.getRow(sheet.getFirstRowNum());
		System.out.println(firstRow.getFirstCellNum() + " - " + firstRow.getLastCellNum());
		System.out.println(firstRow.getCell(firstRow.getFirstCellNum()) + " - " + firstRow.getCell(firstRow.getLastCellNum()));
		printRow(firstRow);
		
		System.out.println("--------------------------------");
		
		System.out.println("第 " + sheet.getLastRowNum() + " 行");//1
		XSSFRow lastRow = sheet.getRow(sheet.getLastRowNum());
		System.out.println(lastRow.getFirstCellNum() + " - " + lastRow.getLastCellNum());
		System.out.println(lastRow.getCell(lastRow.getFirstCellNum()) + " - " + lastRow.getCell(lastRow.getLastCellNum()));
		printRow(lastRow);
		
	}
	
	/**
	 * 测试：6、插入的 空行 是怎么样子的？
	 */
	public static void main02(String[] args) throws IOException {
		File file = new File("ccc.xlsx");
		System.out.println(file.getAbsolutePath());
		
		FileInputStream fis = new FileInputStream(file);
		XSSFWorkbook xwb = new XSSFWorkbook(fis);
		XSSFSheet sheet = xwb.getSheet("aaa3");
		
		System.out.println("第 0 行");//0
		XSSFRow row0 = sheet.getRow(0);
		System.out.println(isBlank(row0)==true ? "是 空行": "不是 空行" );
		System.out.println(row0.getFirstCellNum() + " - " + row0.getLastCellNum());
		System.out.println(row0.getCell(row0.getFirstCellNum()) + " - " + row0.getCell(row0.getLastCellNum()));
		
		System.out.println("--------------------------------");
		
		System.out.println("第 1 行");//1
		XSSFRow row1 = sheet.getRow(1);
		System.out.println(isBlank(row1)==true ? "是 空行": "不是 空行" );
		System.out.println(row1.getFirstCellNum() + " - " + row1.getLastCellNum());
		System.out.println(row1.getCell(row1.getFirstCellNum()) + " - " + row1.getCell(row1.getLastCellNum()));
		
		System.out.println("--------------------------------");
		
		System.out.println("第 2 行");//2
		XSSFRow row2 = sheet.getRow(2);
		System.out.println(isBlank(row2)==true ? "是 空行": "不是 空行" );
		System.out.println(row2.getFirstCellNum() + " - " + row2.getLastCellNum());
		System.out.println(row2.getCell(row2.getFirstCellNum()) + " - " + row2.getCell(row2.getLastCellNum()));
		
		System.out.println("--------------------------------");
		
		System.out.println("第 3 行");//3
		XSSFRow row3 = sheet.getRow(3);
		System.out.println(isBlank(row3)==true ? "是 空行": "不是 空行" );
		System.out.println(row3.getFirstCellNum() + " - " + row3.getLastCellNum());
		System.out.println(row3.getCell(row3.getFirstCellNum()) + " - " + row3.getCell(row3.getLastCellNum()));
		
	}
	
	/**
	 * 判断当前 row 是否为 空行
	 * 空行：
	 * 1、row 为 null
	 * 2、或：row 不为 null，但是 row 中的 cell 全为 blank
	 */
	@SuppressWarnings("deprecation")
	public static boolean isBlank(XSSFRow row) {
		
		if(row == null) {
			return true;
		}else {
			//遍历 cell
			for (int i=row.getFirstCellNum(); i<row.getLastCellNum(); i++) {
				XSSFCell cell = row.getCell(i);
				
				//如果 "cell不为 null" 并且 "cell 不为 BLANK"，那么 当前 row 就不是 blank 的  
				if(cell!=null && cell.getCellType()!=CellType.BLANK.getCode())
					return false;
			}
			return true;
		} 
	}
	
	
	
	
	/**===============================================================*/
	public static void printRow(XSSFRow row) {
		//遍历 cell
		for (int i = 0; i < row.getLastCellNum(); i++) {
			if(i<row.getLastCellNum()) {
				System.out.print(row.getCell(i) + ", ");
			}
		}
		System.out.println();
	}
	
	public static void printRow1(XSSFRow row) {
		//遍历 cell
		for (int i = 0; i < row.getLastCellNum()+2; i++) {
			if(i<row.getLastCellNum()) {
				System.out.print(row.getCell(i) + ", ");
			}else {
				System.out.print(row.getCell(i) + "*, ");
			}
		}
		System.out.println();
	}
	
	/**
	 * 测试：2、"lastCellNum 之前的 空格" VS "lastCellNum 之后的 空格"
	 */
	public static void main1(String[] args) throws IOException {
		File file = new File("ccc.xlsx");
		System.out.println(file.getAbsolutePath());
		
		FileInputStream fis = new FileInputStream(file);
		XSSFWorkbook xwb = new XSSFWorkbook(fis);
		XSSFSheet sheet = xwb.getSheet("bbb");
		
		
		System.out.println("\n----------------------------------------");
		XSSFRow row = sheet.getRow(0);
		printRow(row);
		
	}
	
	/**
	 * 测试：
	 * 3、"lastRowNum 之前的 行" VS "lastRowNum 之后的 行"
	 * 4、要 特别注意 透明的单元格
	 */
	public static void main2(String[] args) throws IOException {
		File file = new File("ccc.xlsx");
		System.out.println(file.getAbsolutePath());
		
		FileInputStream fis = new FileInputStream(file);
		XSSFWorkbook xwb = new XSSFWorkbook(fis);
		XSSFSheet sheet = xwb.getSheet("bbb");
		
		
		System.out.println("\n----------------------------------------");
		
		//遍历 row
		for (int j = 0; j <= sheet.getLastRowNum()+3; j++) {
			XSSFRow row1 = sheet.getRow(j);
			
			if(j<=sheet.getLastRowNum()) {
				System.out.println("第 " + j + " 行：【" + row1 + "】");
			}else {
				System.out.println("第 " + j + " 行：【" + row1 + "*】");
			}
			
			if(row1 != null)
				printRow(row1);
				
			
			System.out.println("=======");
		}
		
	}

	
	/**===============================================================*/
	
	public static void main9(String[] args) throws IOException {
		File file = new File("ccc.xlsx");
		System.out.println(file.getAbsolutePath());
		
		
		FileInputStream fis = new FileInputStream(file);
		XSSFWorkbook xwb = new XSSFWorkbook(fis);
		XSSFSheet sheet = xwb.getSheet("asd");
		System.out.println(sheet.getFirstRowNum());//2
		System.out.println(sheet.getLastRowNum());//10
		
		XSSFRow row = sheet.getRow(sheet.getLastRowNum());
		System.out.println(row);
		
		System.out.println("======================================================");
		
		XSSFRow row1 = sheet.getRow(3);//第 3 行上 没有任何内容，所以 row1 为 null
		System.out.println(row1);
		XSSFRow row2 = sheet.getRow(12);//第 12 行上 没有任何内容，所以 row1 为 null，而且 12>sheet.getLastRowNum()
		System.out.println(row2);
		
		System.out.println("======================================================");
		
		XSSFRow row3 = sheet.getRow(4);
		System.out.println(row3);
		System.out.println(row3.getFirstCellNum());//1
		System.out.println(row3.getLastCellNum());//4
		
		System.out.println("---");
		
		System.out.println(row3.getCell(1));
		System.out.println(row3.getCell(3));
		System.out.println(row3.getCell(4));
	}
}
